from datetime import datetime

from flask import Blueprint, request, flash, render_template, session, redirect, url_for
from app.extensions import db
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import inspect, MetaData, Table, DateTime, Column, String, Integer, Boolean, Float

from app.routes.api import login_required, login_required_html

# 创建 SQL管理蓝图
sqlmanager_bp = Blueprint('sqlmanager_bp', __name__)

# 全局存储数据库元数据
metadata = MetaData()


def get_tables():
    """获取数据库中所有表的列表"""
    inspector = inspect(db.engine)
    return inspector.get_table_names()


def get_table_structure(table_name):
    """获取指定表的结构信息"""
    inspector = inspect(db.engine)
    columns = inspector.get_columns(table_name)

    # 修复：使用正确的方法获取主键约束
    pk_constraint = inspector.get_pk_constraint(table_name)
    primary_keys = pk_constraint['constrained_columns']

    # 为每个字段添加主键标识
    for column in columns:
        column['is_primary'] = column['name'] in primary_keys
        # 检查是否自增
        column['autoincrement'] = column.get('autoincrement', False) or \
                                  (column['name'] in primary_keys and
                                   column['type'].python_type.__name__ in ['int', 'integer'])

    return columns


def get_primary_key_column(table_name):
    """获取表的主键列名"""
    inspector = inspect(db.engine)
    # 修复：使用正确的方法获取主键约束
    pk_constraint = inspector.get_pk_constraint(table_name)
    primary_keys = pk_constraint['constrained_columns']
    return primary_keys[0] if primary_keys else None


def get_table_records(table_name):
    """获取表中的所有记录"""
    table = Table(table_name, metadata, autoload_with=db.engine)
    result = db.session.query(table).all()
    return result


def get_record(table_name, record_id):
    """获取表中的特定记录"""
    table = Table(table_name, metadata, autoload_with=db.engine)
    pk_column = get_primary_key_column(table_name)
    if not pk_column:
        return None

    pk_attr = getattr(table.c, pk_column)
    record = db.session.query(table).filter(pk_attr == record_id).first()
    return record


def delete_record(table_name, record_id):
    """删除表中的特定记录"""
    try:
        table = Table(table_name, metadata, autoload_with=db.engine)
        pk_column = get_primary_key_column(table_name)
        if not pk_column:
            return False

        pk_attr = getattr(table.c, pk_column)
        db.session.query(table).filter(pk_attr == record_id).delete()
        db.session.commit()
        return True
    except SQLAlchemyError:
        db.session.rollback()
        return False


def insert_record(table_name, data):
    """插入新记录"""
    try:
        table = Table(table_name, metadata, autoload_with=db.engine)
        # 过滤掉空值和主键(如果是自增)
        pk_column = get_primary_key_column(table_name)
        if pk_column in data and (data[pk_column] is None or data[pk_column] == ''):
            del data[pk_column]

        # 转换数据类型
        for column in table.columns:
            if column.name in data and data[column.name] != '':
                try:
                    # 尝试转换为列的实际数据类型
                    data[column.name] = column.type.python_type(data[column.name])
                except (ValueError, TypeError):
                    # 转换失败时保持字符串
                    pass

        db.session.execute(table.insert().values(**data))
        db.session.commit()
        return True
    except SQLAlchemyError as e:
        db.session.rollback()
        flash(f'添加记录失败: {str(e)}', 'danger')
        return False


def update_record(table_name, record_id, data):
    """更新记录"""
    try:
        table = Table(table_name, metadata, autoload_with=db.engine)
        pk_column = get_primary_key_column(table_name)
        if not pk_column:
            return False

        # 转换数据类型
        for column in table.columns:
            if column.name in data and data[column.name] != '':
                # 处理日期时间类型字段
                if isinstance(column.type, DateTime):
                    try:
                        # 替换前端传递的'T'为空格，统一格式后再转换
                        date_str = data[column.name].replace('T', ' ')
                        data[column.name] = datetime.strptime(
                            date_str,
                            "%Y-%m-%d %H:%M:%S"  # 匹配转换后的格式
                        )
                    except ValueError:
                        # 转换失败时抛出明确错误
                        flash(f"日期时间格式错误，请使用 '年-月-日 时:分:秒' 格式", 'danger')
                        return False
                else:  # 处理其他数据类型
                    try:
                        # 尝试将输入转换为字段对应的Python类型
                        data[column.name] = column.type.python_type(data[column.name])
                    except (ValueError, TypeError):
                        pass  # 忽略转换失败的情况，保持原始值

        # 执行更新操作
        pk_attr = getattr(table.c, pk_column)
        db.session.execute(
            table.update()
            .where(pk_attr == record_id)
            .values(**data)
        )
        db.session.commit()
        return True
    except SQLAlchemyError as e:
        db.session.rollback()
        flash(f'更新记录失败: {str(e)}', 'danger')
        return False


def drop_table(table_name):
    """删除指定表"""
    try:
        # 检查表是否存在
        if table_name not in get_tables():
            return False, "表不存在"

        # 加载表并删除
        table = Table(table_name, metadata, autoload_with=db.engine)
        table.drop(db.engine)
        metadata.remove(table)  # 从元数据中移除表定义
        db.session.commit()
        return True, "表删除成功"
    except SQLAlchemyError as e:
        db.session.rollback()
        return False, f"数据库错误: {str(e)}"
    except Exception as e:
        return False, f"系统错误: {str(e)}"


@sqlmanager_bp.route('/table/<table_name>/drop', methods=['POST'])
@login_required
def drop_table_route(table_name):
    """删除表的路由"""
    success, msg = drop_table(table_name)
    if success:
        flash(f'成功删除表 {table_name}', 'success')
    else:
        flash(f'删除表失败: {msg}', 'danger')
    return redirect(url_for('sqlmanager_bp.index_sql_page'))


@sqlmanager_bp.route('/table/<table_name>')
@login_required_html
def table_view(table_name):
    """显示表中的所有记录"""
    structure = get_table_structure(table_name)
    records = get_table_records(table_name)
    pk_column = get_primary_key_column(table_name)
    return render_template('/sqlman/table_view.html',
                           table_name=table_name,
                           structure=structure,
                           records=records,
                           pk_column=pk_column)


@sqlmanager_bp.route('/table/<table_name>/add', methods=['GET', 'POST'])
@login_required
def add_record(table_name):
    """添加新记录"""
    structure = get_table_structure(table_name)
    pk_column = get_primary_key_column(table_name)

    if request.method == 'POST':
        data = {}
        for field in structure:
            # 跳过自增主键
            if field['is_primary'] and field.get('autoincrement', False):
                continue
            data[field['name']] = request.form.get(field['name'], '').strip() or None

        if insert_record(table_name, data):
            flash(f'成功添加 {table_name} 记录', 'success')
            return redirect(url_for('sqlmanager_bp.table_view', table_name=table_name))
        else:
            flash('添加记录失败', 'danger')

    return render_template('/sqlman/add.html', table_name=table_name, structure=structure, pk_column=pk_column)


@sqlmanager_bp.route('/table/<table_name>/edit/<record_id>', methods=['GET', 'POST'])
@login_required_html
def edit_record(table_name, record_id):
    """编辑记录"""
    structure = get_table_structure(table_name)
    record = get_record(table_name, record_id)
    pk_column = get_primary_key_column(table_name)

    if not record:
        flash('记录不存在', 'danger')
        return redirect(url_for('sqlmanager_bp.table_view', table_name=table_name))
    record_dict = {}
    for field in structure:
        field_name = field['name']
        # 从记录中提取字段值，兼容不同数据类型（如None、日期等）
        if hasattr(record, field_name):
            # 若记录是对象，用属性访问
            field_value = getattr(record, field_name)
        elif field_name in record:
            # 若记录是字典/Row对象，用键访问
            field_value = record[field_name]
        else:
            field_value = ''  # 字段不存在时默认空值

        # 特殊类型处理（如日期转换为字符串）
        if field_value is None:
            record_dict[field_name] = ''
        elif isinstance(field_value, datetime):
            record_dict[field_name] = field_value.strftime('%Y-%m-%d %H:%M:%S')  # 日期格式化
        else:
            record_dict[field_name] = str(field_value)  # 其他类型转为字符串

    if request.method == 'POST':
        data = {}
        for field in structure:
            # 跳过主键
            if field['is_primary']:
                continue
            data[field['name']] = request.form.get(field['name'], '').strip() or None

        if update_record(table_name, record_id, data):
            flash(f'成功更新 {table_name} 记录', 'success')
            return redirect(url_for('sqlmanager_bp.table_view', table_name=table_name))
        else:
            flash('更新记录失败', 'danger')

    return render_template('/sqlman/edit.html',  # 确保模板路径正确
                           table_name=table_name,
                           structure=structure,
                           record=record_dict,
                           record_id=record_id,
                           pk_column=pk_column)


@sqlmanager_bp.route('/table/<table_name>/delete/<record_id>', methods=['POST'])
@login_required
def delete_record_route(table_name, record_id):
    """删除记录"""
    if delete_record(table_name, record_id):
        flash(f'成功删除 {table_name} 记录', 'success')
    else:
        flash('删除记录失败', 'danger')
    return redirect(url_for('sqlmanager_bp.table_view', table_name=table_name))


@sqlmanager_bp.route('/add_sql')
@login_required_html
def add_sql_page():
    """渲染添加SQL页面"""
    return render_template('/sqlman/add.html')


@sqlmanager_bp.route('/sql_manager')
@login_required_html
def index_sql_page():
    """渲染SQL管理首页，显示所有表"""
    tables = get_tables()
    return render_template('/sqlman/index.html', tables=tables)


def get_table_details(table_name):
    """获取表的详细结构信息（扩展版）"""
    inspector = inspect(db.engine)
    columns = inspector.get_columns(table_name)
    pk_constraint = inspector.get_pk_constraint(table_name)
    foreign_keys = inspector.get_foreign_keys(table_name)
    indexes = inspector.get_indexes(table_name)

    # 补充字段详细信息
    for col in columns:
        col['is_primary'] = col['name'] in pk_constraint['constrained_columns']
        col['foreign_keys'] = [fk for fk in foreign_keys if col['name'] in fk['constrained_columns']]
        col['indexes'] = [idx for idx in indexes if col['name'] in idx['column_names']]
        # 提取类型名称（如INTEGER、VARCHAR(50)）
        col['type_str'] = str(col['type'])
    return {
        'columns': columns,
        'primary_keys': pk_constraint,
        'foreign_keys': foreign_keys,
        'indexes': indexes
    }


def alter_table_add_column(table_name, column_data):
    """为表添加新字段"""
    try:
        table = Table(table_name, metadata, autoload_with=db.engine)
        column_type = column_data['type']
        # 映射前端类型到SQLAlchemy类型
        type_mapping = {
            'INTEGER': Integer,
            'STRING': String,
            'BOOLEAN': Boolean,
            'DATETIME': DateTime,
            'FLOAT': Float
        }
        # 处理长度（如VARCHAR(100)）
        if column_type.startswith('STRING') and '(' in column_type:
            length = int(column_type.split('(')[1].strip(')'))
            sa_type = String(length)
        else:
            sa_type = type_mapping.get(column_type.split('(')[0], String)()

        # 创建新列
        new_column = Column(
            column_data['name'],
            sa_type,
            nullable=not column_data['nullable'],
            default=column_data.get('default')
        )
        # 执行ALTER TABLE
        with db.engine.connect() as conn:
            conn.execute(f"ALTER TABLE {table_name} ADD COLUMN {new_column.compile(dialect=db.engine.dialect)}")
            conn.commit()
        # 更新元数据
        metadata.remove(table)
        return True, "字段添加成功"
    except SQLAlchemyError as e:
        return False, f"添加失败: {str(e)}"


def alter_table_modify_column(table_name, column_name, column_data):
    """修改表字段属性"""
    try:
        table = Table(table_name, metadata, autoload_with=db.engine)
        # 仅支持修改nullable和默认值（SQLite有限支持字段类型修改）
        with db.engine.connect() as conn:
            # 修改可为空属性
            nullable_sql = f"ALTER TABLE {table_name} ALTER COLUMN {column_name} {'DROP' if column_data['nullable'] else 'SET'} NOT NULL"
            conn.execute(nullable_sql)
            # 修改默认值
            if 'default' in column_data and column_data['default'] is not None:
                default_sql = f"ALTER TABLE {table_name} ALTER COLUMN {column_name} SET DEFAULT {repr(column_data['default'])}"
                conn.execute(default_sql)
            conn.commit()
        return True, "字段修改成功"
    except SQLAlchemyError as e:
        return False, f"修改失败: {str(e)}"


# 新增路由
@sqlmanager_bp.route('/table/<table_name>/structure')
@login_required_html
def table_structure(table_name):
    """查看表结构详情"""
    details = get_table_details(table_name)
    return render_template('/sqlman/structure.html',
                           table_name=table_name,
                           details=details)


@sqlmanager_bp.route('/table/<table_name>/structure/add_column', methods=['POST'])
@login_required
def add_column(table_name):
    """添加新字段"""
    data = request.form.to_dict()
    success, msg = alter_table_add_column(table_name, data)
    flash(msg, 'success' if success else 'danger')
    return redirect(url_for('sqlmanager_bp.table_structure', table_name=table_name))


@sqlmanager_bp.route('/table/<table_name>/structure/modify_column/<column_name>', methods=['POST'])
@login_required
def modify_column(table_name, column_name):
    """修改字段属性"""
    data = request.form.to_dict()
    success, msg = alter_table_modify_column(table_name, column_name, data)
    flash(msg, 'success' if success else 'danger')
    return redirect(url_for('sqlmanager_bp.table_structure', table_name=table_name))
